package com.wx.daoImpl;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.dbutils.DbUtils;
import org.junit.runner.Request;

import com.wx.dao.BookDao;
import com.wx.model.BookType;
import com.wx.model.Books;
import com.wx.util.DBUtil;

public class BookDaoImpl implements BookDao {

	@Override
	public List<BookType> queryAll() throws Exception {
		List<BookType> list =new ArrayList<>();
		Connection connection = DBUtil.getConnection();
		PreparedStatement ps = connection.prepareStatement("select * from booktype");
		ResultSet rs = ps.executeQuery();
		while(rs.next()){
			BookType bookType= new BookType(rs.getInt(1), rs.getString(2));
			list.add(bookType);
		}
		return list;
	}

	@Override
	
	//分页查询book
	public List<Books> selectBooks(int pageNum, int pageSize) throws Exception {
		List<Books> list = new ArrayList<>();
		Connection connection = DBUtil.getConnection();
		PreparedStatement ps = connection.prepareStatement("select * from books limit ?,?");
		ps.setInt(1, (pageNum-1)*pageSize);
		ps.setInt(2, pageSize);
		ResultSet rs = ps.executeQuery();
		while(rs.next()){
			Books books = new Books(rs.getInt(1),rs.getInt(2), rs.getString(3), rs.getString(4), rs.getString(5), rs.getDate(6), rs.getString(7), rs.getInt(8), rs.getDouble(9), rs.getInt(10), rs.getString(11));
			list.add(books);
		}
		return list;
	}

	@Override

	public Books bookMsg(int bookid,String username) throws Exception {
		Connection connection = DBUtil.getConnection();
		PreparedStatement ps = connection.prepareStatement("select picture,bookname,price from books where bookid=?");
		ps.setInt(1, bookid);
		ResultSet rs = ps.executeQuery();
		Books book=new Books();
		System.out.println(username);
		PreparedStatement ps1 = connection.prepareStatement("select cartcount from cart where userid=(select userid from users where username=?) and bookid=?");
		ps1.setString(1,username );
		ps1.setInt(2, bookid);
		ResultSet rs1 = ps1.executeQuery();
		if(rs1.next()){
			book.setCartcount(rs1.getInt(1));
		}
		if(rs.next()){
			book.setBookname(rs.getString(2));
			book.setPicture(rs.getString(1));
			book.setPrice(rs.getDouble(3));
		}
		return book;
		}

	public boolean addBook(Books book) throws Exception {
		boolean b= false;
		Connection connection = DBUtil.getConnection();
		PreparedStatement ps = connection.prepareStatement("insert into books(typeid,bookname,author,press,pubtime,dis,bookcount,price,salenum,picture) values(?,?,?,?,?,?,?,?,0,?)");
		ps.setInt(1, book.getTypeid());
		ps.setString(2, book.getBookname());
		ps.setString(3, book.getAuthor());
		ps.setString(4, book.getPress());
		ps.setDate(5, (Date) book.getPubtime());
		ps.setString(6, book.getDis());
		ps.setInt(7, book.getBookcount());
		ps.setDouble(8, book.getPrice());
		ps.setString(9, book.getPicture());
		int i = ps.executeUpdate();
		if(i==1){
			b=true;
		}
		return b;

	}

	@Override
	//按条件查询总数
	public int findCount() throws Exception {
		int sum = 0;
		Connection connection = DBUtil.getConnection();
		PreparedStatement ps = connection.prepareStatement("select count(*) from books");
		ResultSet rs = ps.executeQuery();
		if(rs.next()){
			sum=rs.getInt(1);
		}
		return sum;
	}

	@Override
	//条件分页查询book
	public List<Books> selectBooks(int pageNum, int pageSize, int typeid) throws Exception {
		List<Books> list = new ArrayList<>();
		Connection connection = DBUtil.getConnection();
		PreparedStatement ps = connection.prepareStatement("select * from books where typeid=? limit ?,?");
		ps.setInt(1, typeid);
		ps.setInt(2, (pageNum-1)*pageSize);
		ps.setInt(3, pageSize);
		ResultSet rs = ps.executeQuery();
		while(rs.next()){
			Books books = new Books(rs.getInt(1),rs.getInt(2), rs.getString(3), rs.getString(4), rs.getString(5), rs.getDate(6), rs.getString(7), rs.getInt(8), rs.getDouble(9), rs.getInt(10), rs.getString(11));
			list.add(books);
		}
		return list;
	}

	@Override
	public int findCount(int typeid) throws Exception {
		int sum = 0;
		Connection connection = DBUtil.getConnection();
		PreparedStatement ps = connection.prepareStatement("select count(*) from books where typeid=?");
		ps.setInt(1, typeid);
		ResultSet rs = ps.executeQuery();
		if(rs.next()){
			sum=rs.getInt(1);
		}
		return sum;
	}
	//添加图书类
	@Override
	public boolean addBookType(String typename) throws Exception {
		boolean b= false;
		Connection connection = DBUtil.getConnection();
		PreparedStatement ps = connection.prepareStatement("insert into booktype(typename) values(?)");
		ps.setString(1, typename);
		int rs = ps.executeUpdate();
		if(rs==1){
			b=true;
		}
		return b;
	}

}
